package org.lyu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.lyu.dao.IPetDao;
import org.lyu.entity.Pet;
import org.lyu.util.MyDbUtils1;

/**
 * 增删改查的dao
 * 
 * 
 *JDBC使用总结：
 * 1.sql没有“？”使用Statement，否则PreparedStatement.
 * 
 * 2.Statement，使用" = connection.createStatement();"创建，
 * 	执行修改"int c = statement.executeUpdate(sql);"
 * 	执行修改"ResultSet rs = statement.executeQuery(sql);"
 * 
 * 3.PreparedStatement，使用" = connection.prepareStatement(sql);"创建，
 *  需要使用statement.setXXX覆盖占位符，占位符的标号从1开始。
 * 	执行修改"int c = statement.executeUpdate();"
 * 	执行修改"ResultSet rs = statement.executeQuery();"
 * 
 * 4.获取了ResultSet，使用while (rs.next())循环，结果集第一行指向列名，所以略过第一行。
 * 
 * @author Administrator
 *
 */
public class PetDao implements IPetDao {
		
	/**
	 * orm Object Relation Mapping 对象关系映射
	 * 
	 * @return
	 */
	public List<Pet> getAll() {
		List<Pet> result = new ArrayList<Pet>();

		String sql = "select * from pet";

		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;

		try {

			connection = MyDbUtils1.getConnection();

			statement = connection.createStatement();

			rs = statement.executeQuery(sql);

			while (rs.next()) {

				String name = rs.getString("name");
				String owner = rs.getString("owner");
				String species = rs.getString("species");
				String sex = rs.getString("sex");
				Date birth = rs.getDate("birth");
				Date death = rs.getDate("death");

				result.add(new Pet(name, owner, species, sex, birth, death));

			}

		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			MyDbUtils1.close(rs, statement, connection);
		}

		return result;

	}
		

	
	public int add(Pet pet) {
		String sql = "insert into pet(name,owner,species,sex,birth,death) values " + "(?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement statement = null;
		try {
			// 获取链接
			conn = MyDbUtils1.getConnection();

			// 根据链接conn创建语句
			statement = conn.prepareStatement(sql);

			// 覆盖占位符在的数据
			statement.setString(1, pet.getName());
			statement.setString(2, pet.getOwner());
			statement.setString(3, pet.getSpecies());
			statement.setString(4, pet.getSex());
			statement.setDate(5, pet.getBirth());
			statement.setDate(6, pet.getDeath());

			// 执行更新操作
			int r = statement.executeUpdate();

			return r;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDbUtils1.close(statement, conn);
		}
		return 0;
	}

	public int update(Pet pet) {
		
		String sql = " update pet set owner=?,species=?,sex=?,birth=?,death=? where name = ? ";
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			// 获取链接
			conn = MyDbUtils1.getConnection();

			// 根据链接conn创建语句
			statement = conn.prepareStatement(sql);

			// 覆盖占位符在的数据
			statement.setString(1, pet.getOwner());
			statement.setString(2, pet.getSpecies());
			statement.setString(3, pet.getSex());
			statement.setDate(4, pet.getBirth());
			statement.setDate(5, pet.getDeath());
			
			statement.setString(6, pet.getName());

			// 执行更新操作
			int r = statement.executeUpdate();

			return r;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDbUtils1.close(statement, conn);
		}
		return 0;

	}

	public int delete(String petName) {
		String sql = " delete from pet where name = ? ";
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			// 获取链接
			conn = MyDbUtils1.getConnection();

			// 根据链接conn创建语句
			statement = conn.prepareStatement(sql);

			// 覆盖占位符在的数据
			statement.setString(1, petName);

			// 执行更新操作
			int r = statement.executeUpdate();

			return r;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyDbUtils1.close(statement, conn);
		}
		return 0;
	}

	
	public Pet getByName(String petName) {
		String sql = " select * from pet where name = ? ";

		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;

		try {

			connection = MyDbUtils1.getConnection();

			statement = connection.createStatement();

			rs = statement.executeQuery(sql);

			if (rs.next()) {

				String name = rs.getString("name");
				String owner = rs.getString("owner");
				String species = rs.getString("species");
				String sex = rs.getString("sex");
				Date birth = rs.getDate("birth");
				Date death = rs.getDate("death");

				return new Pet(name, owner, species, sex, birth, death);

			}

		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			MyDbUtils1.close(rs, statement, connection);
		}

		return null;
	}
	
}
